<?php

/**
 * 合同仓库
 * @author wangwx 2015-5-7
 */

namespace common\repositories\myhtsq;

use common\entities\common\MyscrmApplicationEntity;
use common\entities\common\MyscrmContractEntity;
use common\entities\common\MyscrmOrganizationToAuthorizeEntity;
use common\entities\ContractEntity;
use common\entities\myhtsq\ApplicationInfoEntity;
use common\entities\myhtsq\ContractApplicationEntity;
use common\entities\myhtsq\ContractLittlerCategoryEntity;
use common\entities\myhtsq\ContractPaymentEntity;
use common\entities\myhtsq\ContractPropertyEntity;
use common\entities\myhtsq\ContractReceivableEntity;
use common\entities\myhtsq\ErpBusinessUnitEntity;
use common\entities\myhtsq\ErpUserEntity;
use common\entities\myhtsq\MyscrmContractDistributionAmountEntity;
use common\entities\myhtsq\RoleUserEntity;
use common\repositories\BaseRepository;
use common\entities\myhtsq\ContractManageEntity;
use common\models\myhtsq\ContractManageModel;
use common\repositories\common\MyscrmContractRepository;
use common\repositories\common\MyscrmContractSyncDeleteDataRepository;
use common\services\myhtsq\ErpInterfaceService;
use common\support\StringHelper;
use common\support\Helper;
use yii\db\Query;
use common\entities\ConfigEntity;
use yii\helpers\ArrayHelper;
use common\entities\myhtsq\InvoicePaymentEntity;
use common\services\myhtsq\ContractPaymentService;

class ContractManageRepository extends BaseRepository
{

    const SIGN_TIME_TYPE = 1; //签约日期类型
    const INPUT_TIME_TYPE = 2; //录入日期类型
    //const SIGN_ACHIEVE_TIME_TYPE = 3; //签约业绩所属日期类型
    const EFFECT_TIME_TYPE = 3; //生效日期类型
    //const PAYMENT_ACHIEVE_TIME_TYPE = 4; //回款业绩所属日期类型
    const PAYMENT_FILE_TIME_TYPE = 4; //回款业绩所属日期类型


    /**
     * 构造方法
     * @param unknown $config (optional)
     */
    public function __construct($config = [])
    {
        parent::__construct($config);
    }

    /**
     * 保存合同
     * @param \common\models\myhtsq\ContractManageModel $contractModel 合同模型
     * @param bool $isComeFromBusinessSystem 是否来自商机系统
     * @return boolean|string
     */
    public static function saveContract(ContractManageModel $contractModel, $isComeFromBusinessSystem = false)
    {
        $contractManageEntity = new ContractManageEntity();

        $data = $contractModel->attributes;
        $contractManageEntity->load($data, '');
        if ($isComeFromBusinessSystem === false) {//如果不是来自商机系统，则合同ID由我们生成，如果是来自商机系统，合同ID由对方提供
            $contractManageEntity->contract_id = StringHelper::uuid();
        }

        //合同编号规则  组合规则：区域（SH）-客户简称（客户拼音字母简称，从商机系统获取）- FW20150102流水（0001）
        if ($contractManageEntity->save()) {
            return $contractManageEntity->contract_id;
        } else {
            return false;
        }
    }

    /**
     * 更新合同
     * @param ContractManageModel $contractModel
     * @return string|boolean
     */
    public static function updateContract(ContractManageModel $contractModel)
    {

        $contractManageEntity = new ContractManageEntity();

        $contractModelInfo = $contractManageEntity::find()->where(['contract_id' => $contractModel->contract_id])->one();
        $data = $contractModel->attributes;

        $contractModelInfo->load($data, '');
        $contractModelInfo->is_violated = intval($contractModelInfo->is_violated);
        if ($contractModelInfo->save()) {
            return $contractModelInfo->contract_id;
        } else {
            return false;
        }
    }

    /**
     * 获取合同编号
     *
     * @param     $contractId  string  合同ID
     * @return    bool|string
     */
    public static function getContractErpCode($contractId)
    {
        return ContractManageEntity::find()->select('erp_contract_code')->where(['contract_id' => $contractId])->scalar();
    }

    /**
     * 获取合同ERP-ID
     *
     * @param     $contractId  string  合同ID
     * @return    bool|string
     */
    public static function getContractErpId($contractId)
    {
        return ContractManageEntity::find()->select('erp_contract_id')->where(['contract_id' => $contractId])->scalar();
    }


    /**
     * 获取合同签约人
     *
     * @param     $contractId  string  合同ID
     * @return    bool|string
     */
    public static function getContractSignPeopleId($contractId)
    {
        return ContractManageEntity::find()->select('sign_people_id')->where(['contract_id' => $contractId])->scalar();
    }

    /**
     * 合同是否已审批
     * @param string $contractId 合同id
     * @return boolean
     */
    public static function isApproveStatus($contractId)
    {
        return ContractManageEntity::find()->where(['contract_status' => ContractManageEntity::APPROVE_STATUS_NO_HANDLE, 'contract_id' => $contractId])->exists();
    }

    /**
     * 获取单条合同model
     * @param string $contractId 合同id
     * @return array|\common\entities\myhtsq\ContractManageEntity
     */
    public static function getContractManageModelByContractId($contractId)
    {
        return ContractManageEntity::find()->where(['contract_id' => $contractId])->one();
    }


    /**
     * 获取单条合同model
     * @param string $contractId 合同id
     * @return array|\common\entities\myhtsq\ContractManageEntity
     */
    public static function getContractManageModelByContractNum($contractCode)
    {
        return ContractManageEntity::find()->where(['contract_code' => $contractCode])->one();
    }

    /**
     * 获取单条合同model
     * @param string $contractId 合同id
     * @return array|\common\entities\myhtsq\ContractManageEntity
     */
    public static function getContractIdByContractNum($contractCode)
    {
        $result = ContractManageEntity::find()->select('contract_id')->where(['contract_code' => $contractCode])->one();
        return $result['contract_id'];
    }

    /**
     * 获取ERP关联的合同
     *
     * @param $erpContractId
     * @return array|null|\yii\db\ActiveRecord
     */
    public static function getContractManageByErpContractId($erpContractId)
    {
        return ContractManageEntity::find()->where(['erp_contract_id' => $erpContractId])->one();
    }

    /**
     * 根据erp合同编号获取ERP关联的合同
     *
     * @param $erpContractIdCode
     * @return array|null|\yii\db\ActiveRecord
     */
    public static function getContractManageByErpContractCode($erpContractIdCode)
    {
        return ContractManageEntity::find()->where(['erp_contract_code' => $erpContractIdCode])->one();
    }


    /**
     * 获取单条合同model
     * @param string $contractId 合同id
     * @return array|\common\entities\myhtsq\ContractManageEntity
     */
    public static function getContractManageListByContractId($contractId)
    {
        return ContractManageEntity::find()->where(['contract_id' => $contractId])->asArray()->one();
    }

    /**
     * 获取单条合同model
     * @param string $signId 会签ID
     * @return array|\common\entities\myhtsq\ContractManageEntity
     */
    public static function getContractManageBySignId($signId)
    {
        return ContractManageEntity::find()->where(['sign_id' => $signId])->asArray()->one();
    }

    /**
     * 更改合同状态
     * @param string $contractId 合同id
     * @param int $status 合同状态
     * @return boolean
     */
    public static function updateContractStatus($contractId, $status)
    {
        return ContractManageEntity::updateAll(['contract_status' => $status], ['contract_id' => $contractId]);
    }

    /**
     * 更新合同录入日期
     * @param $contractId
     */
    public static function updateContractInputDate($contractId)
    {
        $entity = ContractManageEntity::find()->where(['contract_id' => $contractId])->one();
        $entity->effect_date = date('Y-m-d');
        $entity->save();
    }

    /**
     * 合同作废
     * @param string $contractId 合同id
     * @return boolean
     */
    public static function updateContractCancelledStatusByContractId($contractId)
    {
        return ContractManageEntity::updateAll(['is_cancelled' => ContractManageEntity::CONTRACT_IS_CANCELLED], ['contract_id' => $contractId]);
    }

    /**
     * 获取存在合同的城市区域id列表
     * @param $area_id 公司ID
     * @return array
     */
    public static function getContractAreaIdList($area_id)
    {
        $query = ContractManageEntity::find()->select('area_id');
        if (!empty($area_id)) {
            $query->andWhere(['area_id' => $area_id]);
        }
        return $query->groupBy('area_id')->all();
    }

    /**
     * 获取合同列表
     * @param \common\support\PageParam $pageParam 分页参数对象
     * @param int $status 合同状态  合同状态:0：草稿,1：提交复核,2：已审核  -1作废
     * @param string $property 合同性质
     * @param string $keyword 关键字
     * @param integer $areaId 区域id
     * @param string $signPeopleId 签约人ID
     * @param                          $timeType
     * @param                          $timeStart
     * @param                          $timeEnd
     * @param bool $chooseAllArea
     * @param integer   $category
     * @return array
     */
    public static function getContractList($pageParam, $status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd, $chooseAllArea = false, $payeeIds = null,$category)
    {
        $query = self::getContractListQuery($pageParam, $status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd, $chooseAllArea, $payeeIds,$category);

        $count = $query->count(); //条数
        $offset = Helper::getSkipByPageParam($pageParam);
        $query->offset($offset)->limit($pageParam->pageSize); //分页
        $items = $query->asArray()->all();
        return Helper::setPageResult($count, $items);
    }

    /**
     * 获取查询query
     * @param $pageParam
     * @param $status
     * @param $property
     * @param $keyword
     * @param $areaId
     * @param $signPeopleId
     * @param $timeType
     * @param $timeStart
     * @param $timeEnd
     * @param bool $chooseAllArea
     * @param integer $category
     * @return \yii\db\ActiveQuery
     */
    private static function getContractListQuery($pageParam, $status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd, $chooseAllArea = false, $payeeIds = null,$category)
    {
        $query = ContractManageEntity::find();
        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);


        //区域id
        if (!$chooseAllArea || empty($signPeopleId)) {
            $query->andFilterWhere(['area_id' => $areaId]);
        }
        if ($category > 0){
            switch ($category){
                //广告合同 = 云客广告服务
                case '1':
                    $litter_category_id = ContractLittlerCategoryRepository::getLittlerCategoryIdByName('云客广告服务');
                    $query->andWhere(['littler_category_id'=>$litter_category_id]);
                    break;
                //SaaS合同 = 云客租用服务+云客增值服务
                case '2':
                    $litter_category_id_1 = ContractLittlerCategoryRepository::getLittlerCategoryIdByName('云客增值服务');
                    $litter_category_id_2 = ContractLittlerCategoryRepository::getLittlerCategoryIdByName('云客租用服务');
                    $query->andWhere(['in','littler_category_id',[$litter_category_id_1,$litter_category_id_2]]);
                    break;
                case '3':
                    $query->andWhere(['littler_category_id'=>'']);
                default:break;
            }
        }


        if (!empty($keyword)) {//搜索关键字  公司/公司简称/合同编号/客户全称//客户简称//顶级客户简称
            $query->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');
            self::setKeywordQuery($query, $keyword);
        }

        // 时间类型
        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                /*case self::SIGN_ACHIEVE_TIME_TYPE:
                    $query->innerJoin('myscrm_sign_achievement', 'myscrm_contract_manage.contract_id = myscrm_sign_achievement.contract_id');
                    $query->andWhere(['>', 'myscrm_sign_achievement.achievement_belong_time', $timeStart]);
                    $query->andWhere(['<', 'myscrm_sign_achievement.achievement_belong_time', $timeEnd]);
                    break;*/
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
                /*case self::PAYMENT_ACHIEVE_TIME_TYPE:
                    $query->innerJoin('myscrm_payment_achievement', 'myscrm_contract_manage.contract_id = myscrm_payment_achievement.contract_id');
                    $query->andWhere(['>', 'myscrm_payment_achievement.achievement_belong_time', $timeStart]);
                    $query->andWhere(['<', 'myscrm_payment_achievement.achievement_belong_time', $timeEnd]);
                    break;*/
            }
        }

        //从 myscrm_contract_receivable 根据payee_id取 contract_id
        $contractIds = ContractReceivableRepository::getContractIdsByPayeeIds($payeeIds);
        //签约人
        $query->andFilterWhere(['or',
            ['sign_people_id' => $signPeopleId],
            ['contract_id' => $contractIds]
        ]);
        return $query;
    }

    /**
     * 获取结算合同列表
     * @param \common\support\PageParam $pageParam 分页参数对象
     * @param int $status 结算状态 0：未结算 1：已结算
     * @param string $property 合同性质
     * @param integer $areaId 区域id
     * @param string $keyword 关键字
     * @param string $beginDate 结算开始时间
     * @param string $endDate 结算结束时间
     */
    public static function getBalanceContractList($pageParam, $status, $property, $keyword, $areaId, $beginDate, $endDate)
    {
        $query = ContractManageEntity::find();

        //合同结算状态
        if (!empty($status) || $status === 0) {
            $query->andWhere(['balance_status' => $status]);
        }

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //结算时间-开始时间
        $query->andFilterWhere(['>=', 'balance_date', $beginDate]);

        //结算时间-结束时间
        $query->andFilterWhere(['<=', 'balance_date', $endDate]);

        //搜索关键字  公司/公司简称/合同编号/客户全称
        if (!empty($keyword)) {
            $query->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id= myscrm_contract_company.company_id');
            self::setKeywordQuery($query, $keyword);
        }

        $query->andWhere(['is_cancelled' => ContractManageEntity::CONTRACT_NO_CANCELLED]);

        $count = $query->count(); //条数
        $offset = Helper::getSkipByPageParam($pageParam);
        $query->offset($offset)->limit($pageParam->pageSize); //分页
        $items = $query->asArray()->all();
        return Helper::setPageResult($count, $items);
    }

    /**
     * 获取导出未结算合同列表
     * @param boolean $property 合同性质
     * @param string $keyword 关键字
     * @param integer $areaId 区域id
     * @param string $beginDate 结算开始时间
     * @param string $endDate 结算结束时间
     */
    public static function getExportBalanceContractListExecl($property, $keyword, $areaId, $beginDate, $endDate)
    {
        $query = ContractManageEntity::find();

        $query->leftJoin('myscrm_erp_business_unit', 'myscrm_contract_manage.company_id = myscrm_erp_business_unit.BUGUID');

        $query->andWhere(['balance_status' => ContractManageEntity::CONTRACT_NOT_BALANCE_STATUS]);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //返款时间-开始时间
        $query->andFilterWhere(['>=', 'repayment_time', $beginDate]);

        //返款时间-结束时间
        $query->andFilterWhere(['<=', 'repayment_time', $endDate]);

        $query->andWhere(['is_cancelled' => ContractManageEntity::CONTRACT_NO_CANCELLED]);

        //搜索关键字  公司/公司简称/合同编号
        if (!empty($keyword)) {
            $query->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');
            self::setKeywordQuery($query, $keyword);
        }

        return $query->asArray()->all();
    }

    /**
     * 特批授权界面获取合同select2控件下拉选项
     * @param string $keyword 搜索关键字
     * @param string $orgId 租户Id
     * @param string $limit 分页
     */
    public static function getContractListForSepcialAuthorizeSelect2($keyword, $orgId, $limit = 10)
    {
        if (is_null($keyword) || empty($keyword) || is_null($orgId) || empty($orgId)) {
            return [];
        }

        $models = ContractManageEntity::find()
            ->select('contract_id,contract_code as id,contract_code as text')
            ->where(['like', 'contract_code', $keyword])
            //813付小玲需求，已绑定的合同还可以选
            //->andWhere(['not exists', $query->select('contract_code')->from('myscrm_special_approval_authorize')->where('myscrm_special_approval_authorize.contract_code=myscrm_contract_manage.contract_code')])
            ->orderBy(['contract_code' => SORT_ASC])
            ->groupBy("contract_code")
            ->limit($limit)
            ->asArray()
            ->all();
        $contracts = MyscrmContractRepository::getContractsByOrgId($orgId);
        $contratNums = ArrayHelper::getColumn($contracts, "ContractNum", false);

        if (!empty($models)) {
            foreach ($models as $k => $v) {
                if(!in_array($v["id"],$contratNums)){
                    unset($models[$k]);
                }
            }
        }
        return array_values($models);
    }

    /**
     * 违规合同界面获取合同select2控件下拉选项
     * @param string $keyword 搜索关键字
     * @param string $orgId 租户Id
     * @param string $page 分页
     */
    public static function getContractListForViolatedContractSelect2($keyword, $limit = 10)
    {
        if (is_null($keyword) || empty($keyword)) {
            return [];
        }

        $query = new Query();
        return ContractManageEntity::find()
            ->select('contract_id,contract_code as id,contract_code as text')
            ->where(['like', 'contract_code', $keyword])
            ->orderBy(['contract_code' => SORT_ASC])
            ->limit($limit)
            ->asArray()
            ->all();
    }

    /**
     * 检查是否已存在合同编号
     * @param string $contractCode 合同编号
     * @return boolean
     */
    public static function isExistContractCodeByContractCode($contractCode)
    {
        return ContractManageEntity::find()->where(['contract_code' => $contractCode])->exists();
    }

    /**
     * 处理合同结算状态
     * @param string $contractId 合同id
     * @param int $status 合同结算状态
     * @return boolean
     */
    public static function handleContractBalanceStatus($contractId, $status)
    {
        $status = intval($status);
        $contractManageEntity = self::getContractManageModelByContractId($contractId);
        if ($contractManageEntity->balance_status == $status) {
            return true;
        } else {

            $contractManageEntity->balance_status = $status; //结算状态
            if ($status == ContractManageEntity::CONTRACT_BALANCE_STATUS) {
                $contractManageEntity->balance_date = ContractEntity::getDbNowDate(); //结算时间
            }
            return $contractManageEntity->save();
        }
    }

    /**
     * 设置状态查询
     * @param type $query
     * @param type $status
     */
    public static function setStatusQuery(&$query, $status)
    {

        if ($status !== '') {
            //合同状态
            if ($status == -1) {
                $query->andWhere(['is_cancelled' => ContractManageEntity::CONTRACT_IS_CANCELLED]);
            } else {
                $query->andWhere(['contract_status' => $status, 'is_cancelled' => ContractManageEntity::CONTRACT_NO_CANCELLED]);
            }
        }
    }

    /**
     * 设置关键字查询
     * @param type $query
     * @param type $keyword
     */
    public static function setKeywordQuery(&$query, $keyword)
    {
        if (!empty($keyword)) {//搜索关键字  客户全称/公司/合同编号/erp合同编号/会签编号
            $query->andWhere(['or',
                ['like', 'myscrm_contract_manage.customer_full_name', $keyword],
                ['like', 'myscrm_contract_manage.order_code', $keyword],
                ['like', 'myscrm_contract_company.company_name', $keyword],
                ['like', 'myscrm_contract_manage.contract_code', $keyword],
                ['like', 'myscrm_contract_manage.customer_name', $keyword],
                ['like', 'myscrm_contract_manage.erp_contract_code', $keyword],
                ['like', 'myscrm_contract_manage.sign_number', $keyword],
                ['like', 'myscrm_contract_manage.top_customer_name', $keyword]]);
        }
    }

    /**
     * 导出合同
     * @param $status
     * @param $property
     * @param $keyword
     * @param $areaId
     * @param $signPeopleId
     * @param $timeType
     * @param $timeStart
     * @param $timeEnd
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getContractListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = ContractManageEntity::find()
            ->select('myscrm_contract_manage.*, myscrm_contract_company.company_name,'
                . 'myscrm_sign_dely.belong_by, myscrm_sign_dely.department_by, myscrm_sign_dely.dely_on ');

        $query->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            //->leftJoin('(select * from myscrm_contract group by ContractNum,OrganizationId) as myscrm_contract', 'myscrm_contract.ContractNum=myscrm_contract_manage.contract_code')
            ->leftJoin('myscrm_sign_dely', 'myscrm_sign_dely.contract_id= myscrm_contract_manage.contract_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 时间类型
        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }
        return $query->asArray()->all();
    }

    /**
     * 导出合同产品
     * @param $status
     * @param $property
     * @param $keyword
     * @param $areaId
     * @param $signPeopleId
     * @param $timeType
     * @param $timeStart
     * @param $timeEnd
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getContractProductListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.*, myscrm_contract_company.company_name,application.*,myscrm_application_info.*,myscrm_application_price.price as price_standard");

        $query->innerJoin('myscrm_authorization_achievement application', 'application.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_application_info', 'myscrm_application_info.application_id =application.application_id')
            ->leftJoin("myscrm_application_price", "myscrm_application_info.application_id =myscrm_application_price.application_id")
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        $query->where(
            ['or' ,
                ['=' , 'is_del' , 1] ,
                ['is_del' => null]
            ]

        );

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        // 时间类型
        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        $query->indexBy('id');
        return $query->asArray()->all();
    }


    /**
     * 导出已开票未回款明细表
     */
    public static function getContractNoPaymentListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.*, myscrm_contract_company.company_name, myscrm_invoice.*, myscrm_invoice.customer_name as invoice_name");

        $query->innerJoin('myscrm_invoice', 'myscrm_invoice.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        $query->andWhere(['is_red_invoice' => 0, 'is_reded_invoice' => 0]);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        $query->indexBy('invoice_id');

        return $query->asArray()->all();
    }


    /**
     * 导出合同收入
     */
    public static function getContractRevenueListForExport($status, $property, $keyword, $areaId, $signPeopleId ,$timeType = 0, $timeStart = '', $timeEnd = '')
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.sign_date,myscrm_contract_manage.input_date,myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_contract_manage.contract_property_id, myscrm_contract_manage.category_id"
                . ",myscrm_contract_manage.littler_category_id, myscrm_contract_company.company_name"
                . ",myscrm_contract_manage.area_id,myscrm_buss_account_types.type_name,revenue.*");

        $query->innerJoin('myscrm_contract_revenue revenue', 'revenue.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_buss_account_types', 'myscrm_buss_account_types.type_id=revenue.revenue_type')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        // 时间类型
        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'revenue_type' => SORT_ASC]);
        $query->indexBy('revenue_id');
        $models=$query->asArray()->all();
        if (!empty($models)) {
            foreach ($models as $k => $v) {
                $models[$k]["area_name"] = ErpBusinessUnitRepository::getBunameByBuguid($v["area_id"]);
                $contractModel = MyscrmContractRepository::getContractByContractNum($v["contract_code"]);
                $beginOn = "";
                $endOn = "";
                if (!empty($contractModel)) {
                    $beginOn = $contractModel->BeginOn;
                    $endOn = $contractModel->EndOn;
                }
                $models[$k]["BeginOn"] = $beginOn;
                $models[$k]["EndOn"] = $endOn;
            }
        }
        return $models;
    }

    /**
     * 导出合同交付业绩
     */
    public static function getContractDelyListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.top_customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code, myscrm_contract_manage.big_category_id"
                . ", myscrm_contract_manage.area_id, myscrm_contract_manage.customer_property, myscrm_contract_manage.littler_category_id, myscrm_contract_manage.contract_property_id, myscrm_contract_manage.category_id, myscrm_contract_manage.sign_people_id"
                . ", myscrm_contract_manage.sign_people_department_by, myscrm_contract_manage.sign_date, myscrm_contract_manage.input_date, myscrm_contract_manage.sign_amount,myscrm_sign_dely.*,myscrm_sign_dely.attribute_name as sd_attribute_name, myscrm_contract_company.company_name");

        $query->innerJoin('myscrm_sign_dely', 'myscrm_sign_dely.contract_id = myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }


        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        $query->indexBy('dely_id');

        return $query->asArray()->all();
    }

    /**
     * 导出合同未上传标的物明细表
     */
    public static function getContractNoUploadTypeForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = ContractManageEntity::find()
            /*->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.contract_code"
                . ", myscrm_contract_manage.area_id, myscrm_contract_manage.customer_property, myscrm_contract_manage.sign_people_id,myscrm_contract_manage.balance_amount"
                . ", myscrm_contract_manage.sign_date, myscrm_contract_manage.input_date, myscrm_contract_manage.sign_amount, myscrm_contract_company.company_name"
                . ", myscrm_contract_manage.contract_property_id"
                .", COUNT( IF ( myscrm_document.document_type != 4,NULL,myscrm_document.document_type)) AS document_type_4"
            );*/

            ->select([
                'myscrm_contract_manage.contract_id',
                'myscrm_contract_manage.customer_name',
                'myscrm_contract_manage.contract_code',
                'myscrm_contract_manage.area_id',
                'myscrm_contract_manage.customer_property',
                'myscrm_contract_manage.sign_people_id',
                'myscrm_contract_manage.balance_amount',
                'myscrm_contract_manage.sign_date',
                'myscrm_contract_manage.input_date',
                'myscrm_contract_manage.sign_amount',
                'myscrm_contract_company.company_name',
                'myscrm_contract_manage.contract_property_id',
                'myscrm_contract_manage.contract_code',
                'COUNT( IF ( myscrm_document.document_type != 4 ,NULL, myscrm_document.document_type)) AS document_type_4',
            ]);

        $query->leftJoin('myscrm_document', 'myscrm_document.business_id = myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        // 时间大于2015-02-20
        $query->andWhere(['>', 'myscrm_contract_manage.created_on', '2015-02-20']);
        // 只包含代理模式三类
        $query->andWhere('myscrm_contract_manage.contract_property_id  in ("54f8a2b7-fce7-11e4-bed8-00155d02c832","7f828b1b-fce8-11e4-bed8-00155d02c832","7f856e81-fce8-11e4-bed8-00155d02c832")');
        $query->groupBy('`myscrm_contract_manage`.`contract_id`');
        // 未上传4:标的物
        //$query->andWhere('myscrm_contract_manage.contract_id not in (select business_id from myscrm_document where document_type = 4)');

        return $query->asArray()->all();
    }

    /**
     * 导出合同应收款项
     */
    public static function getContractReceivableListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.*"
                . ",receivableItem.receivable_item_name,categoryItem.money_category_item_name"
                . ",myscrm_buss_account_types.type_name as revenue_type_name,receivable.*, myscrm_contract_company.company_name");

        $query->innerJoin('myscrm_contract_receivable receivable', 'receivable.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_receivable_item receivableItem', 'receivable.receivable_item_id = receivableItem.receivable_item_id')
            ->leftJoin('myscrm_money_category_item categoryItem', 'receivable.money_category_item_id = categoryItem.money_category_item_id')
            ->leftJoin('myscrm_buss_account_types', 'receivable.revenue_type = myscrm_buss_account_types.type_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'revenue_type' => SORT_ASC]);
        $query->indexBy('receivable_id');
        $models = $query->asArray()->all();
        if (!empty($models)) {
            foreach ($models as $k => $v) {
                $models[$k]["payee_name"] = ErpUserRepository::getUserNameByBuguid($v["payee_id"]);
            }
        }
        return $models;
    }

    /**
     * 导出合同发票
     */
    public static function getContractInvoiceListForExport($status, $property, $keyword, $areaId, $signPeopleId)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_invoice.*,invoiceCompany.BUName");

        $query->innerJoin('myscrm_invoice', 'myscrm_invoice.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_invoice_business_unit invoiceCompany', 'myscrm_invoice.invoice_company_id = invoiceCompany.BUGUID')
            ->leftJoin('(select * from myscrm_contract group by ContractNum,OrganizationId) as myscrm_contract', 'myscrm_contract.ContractNum=myscrm_contract_manage.contract_code')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        $query->andWhere(['is_main_invoice' => 0]);
        $query->orderBy(['contract_code' => SORT_ASC, 'invoice_code' => SORT_ASC]);
        $query->indexBy('invoice_id');
        $models = $query->asArray()->all();
        if (!empty($models)) {
            foreach ($models as $k => $v) {
                $user = ErpUserRepository::getUserById($v["proposer_id"]);
                if (!empty($user)) {
                    $userName = $user->UserCode;
                    $userCode = $user->UserCode;
                }
                $models[$k]["UserName"] = $userName;
                $models[$k]["UserCode"] = $userCode;

                $models[$k]["area_name"] = ErpBusinessUnitRepository::getBunameByBuguid($v["area_id"]);
            }
        }
        return $models;
    }

    /**
     * 导出合同回款
     */
    public static function getContractPaymentListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        //字段可能存在重复值 需要重命名
        $paymentFieldArr = [
            "payment_id" => "payment_id",
            "finance_code" => "finance_code",
            "register_date" => "register_date",
            "payment_amount" => "payment_amount",
            "payment_date" => "payment_date",
            "payment_code" => "payment_code",
            "receivables_account" => "receivables_account",
            "receivables_account_name" => "receivables_account_name",
            "payment_account_name" => "payment_account_name",
            "remark" => "remark",
            "created_by" => "created_by",
            "modified_by" => "modified_by",
            "created_on" => "created_on",
            "modified_on" => "modified_on",
            "input_date" => "payment_input_date",
        ];
        $paymentFieldStr = "";
        foreach ($paymentFieldArr as $key => $val) {
            $paymentFieldStr .= ",myscrm_contract_payment.$key as $val";
        }
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.*, myscrm_contract_company.company_name"
                . "$paymentFieldStr");

        $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_payment.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        // 时间类型
        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'payment_code' => SORT_ASC]);
        $query->indexBy('payment_id');

        $models= $query->asArray()->all();
        if(!empty($models)){
            foreach ($models as $k=>$v){
                $models[$k]["area_name"]=ErpUserRepository::getUserNameByBuguid($v["area_id"]);
            }
        }
        return $models;
    }

    /**
     * 导出合同签约业绩
     */
    public static function getContractSignAchievementListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = MyscrmContractDistributionAmountEntity::find()
            ->select("myscrm_contract_manage.*,myscrm_sign_achievement.*,myscrm_sign_achievement.attribute_name as sa_attribute_name,
             myscrm_contract_company.company_name, cda.amount_id, cda.sign_average_amount,cda.settlement_average_amount,
             cda.area_id as contractDistributionAmountAreaId,
             cda.user_id as contractDistributionAmountUserId,
             cda.department_id as contractDistributionAmountDepartmentId,
             cda.contract_type as contractDistributionAmountContractType
             ")
            ->from('myscrm_contract_distribution_amount cda');

        $query->innerJoin('myscrm_sign_achievement', 'myscrm_sign_achievement.contract_id= cda.contract_id')
            ->leftJoin('myscrm_contract_manage', 'myscrm_contract_manage.contract_id=cda.contract_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['myscrm_contract_manage.area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        /*// 时间类型
        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_ACHIEVE_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_sign_achievement.achievement_belong_time', $timeStart]);
                    $query->andWhere(['<', 'myscrm_sign_achievement.achievement_belong_time', $timeEnd]);
                    break;
            }
        }*/

        // 时间类型
        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'myscrm_sign_achievement.created_on' => SORT_ASC]);
        $query->indexBy('amount_id');
        $models = $query->asArray()->all();
        if (!empty($models)) {
            foreach ($models as $k => $v) {
                $models[$k]["manager_name"]=ErpUserRepository::getUserNameByBuguid($v["manager_by"]);
                $models[$k]["achievementarea_name"]=ErpBusinessUnitRepository::getBunameByBuguid($v["area_by"]);
                $models[$k]["department_name"]=ErpBusinessUnitRepository::getBunameByBuguid($v["department_by"]);
                $models[$k]["erp_belong_name"]=ErpUserRepository::getUserNameByBuguid($v["erp_achievement_belong"]);
                $models[$k]["erp_belong_abu"]=ErpBusinessUnitRepository::getBunameByBuguid($v["erp_achievement_belong_abu"]);
                $models[$k]["erp_belong_abu_owner"]=ErpUserRepository::getUserNameByBuguid($v["erp_achievement_belong_abu_owner"]);
                $models[$k]["erp_belong_bu_manager"]=ErpUserRepository::getUserNameByBuguid($v["erp_achievement_belong_bu_manager"]);
                $models[$k]["erp_belong_bu"]=ErpBusinessUnitRepository::getBunameByBuguid($v["erp_achievement_belong_bu"]);
            }
        }
        return $models;
    }

    /**
     * 导出合同回款业绩
     */
    public static function getContractPaymentAchievementListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.*,myscrm_payment_achievement.*,myscrm_payment_achievement.attribute_name as pa_attribute_name"
                . ",myscrm_receivable_item.receivable_item_name,myscrm_money_category_item.money_category_item_name"
                . ",myscrm_contract_company.company_name");

        $query->innerJoin('myscrm_payment_achievement', 'myscrm_payment_achievement.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_receivable_item', 'myscrm_receivable_item.receivable_item_id= myscrm_payment_achievement.receivable_item_id')
            ->leftJoin('myscrm_money_category_item', 'myscrm_money_category_item.money_category_item_id= myscrm_payment_achievement.money_category_item_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['myscrm_contract_manage.area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        /*// 时间类型
        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::PAYMENT_ACHIEVE_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_payment_achievement.achievement_belong_time', $timeStart]);
                    $query->andWhere(['<', 'myscrm_payment_achievement.achievement_belong_time', $timeEnd]);
                    break;
            }
        }*/

        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'myscrm_payment_achievement.created_on' => SORT_ASC]);
        $query->indexBy('achievement_id');
        $models = $query->asArray()->all();
        if (!empty($models)) {
            foreach ($models as $k => $v) {
                $models[$k]["belong_name"]=ErpUserRepository::getUserNameByBuguid($v["belong_by"]);
                $models[$k]["manager_name"]=ErpUserRepository::getUserNameByBuguid($v["manager_by"]);
                $models[$k]["achievementarea_name"]=ErpBusinessUnitRepository::getBunameByBuguid($v["area_by"]);
                $models[$k]["department_name"]=ErpBusinessUnitRepository::getBunameByBuguid($v["department_by"]);
                $models[$k]["erp_belong_name"]=ErpUserRepository::getUserNameByBuguid($v["erp_achievement_belong"]);
                $models[$k]["erp_belong_abu"]=ErpBusinessUnitRepository::getBunameByBuguid($v["erp_achievement_belong_abu"]);
                $models[$k]["erp_belong_abu_owner"]=ErpUserRepository::getUserNameByBuguid($v["erp_achievement_belong_abu_owner"]);
                $models[$k]["erp_belong_bu_manager"]=ErpUserRepository::getUserNameByBuguid($v["erp_achievement_belong_bu_manager"]);
                $models[$k]["erp_belong_bu"]=ErpBusinessUnitRepository::getBunameByBuguid($v["erp_achievement_belong_bu"]);
            }
        }
        return $models;
    }

    /**
     * 导出合同回款业绩
     */
    public static function getContractDocumentListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_contract_document.*"
                . ",myscrm_contract_manage.area_id");

        $query->innerJoin('myscrm_contract_document', 'myscrm_contract_document.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'document_name' => SORT_ASC]);
        $query->indexBy('document_id');
        $models = $query->asArray()->all();
        if (!empty($models)) {
            foreach ($models as $k => $v) {
                $models[$k]["keeper_name"]=ErpUserRepository::getUserNameByBuguid($v["keeper"]);
                $models[$k]["area_name"]=ErpBusinessUnitRepository::getBunameByBuguid($v["area_id"]);
            }
        }
        return $models;
    }

    /**
     * 导出违规合同
     */
    public static function getContractViolatedListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_violated_contract.*"
                . ",myscrm_violated_category.category_name,myscrm_violated_littler_category.littler_category_name"
                . ",myscrm_contract_manage.area_id");

        $query->innerJoin('myscrm_violated_contract', 'myscrm_violated_contract.contract_code= myscrm_contract_manage.contract_code')
            ->leftJoin('myscrm_violated_category', 'myscrm_violated_category.violated_category_id = myscrm_violated_contract.violated_category_id')
            ->leftJoin('myscrm_violated_littler_category', 'myscrm_violated_littler_category.violated_littler_category_id = myscrm_violated_contract.violated_littler_category_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        $query->orderBy(['myscrm_contract_manage.contract_code' => SORT_ASC]);
        $query->indexBy('violated_contract_id');
        $models = $query->asArray()->all();
        if (!empty($models)) {
            foreach ($models as $k => $v) {
                $models[$k]["principal_name"] = ErpUserRepository::getUserNameByBuguid($v["violated_principal"]);
                $models[$k]["approver_name"] = ErpUserRepository::getUserNameByBuguid($v["approver"]);
                $models[$k]["area_name"] = ErpBusinessUnitRepository::getBunameByBuguid($v["area_id"]);
            }
        }
        return $models;
    }

    /**
     * 导出特批发票
     */
    public static function getContractSpecialInvoiceListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_special_approval_invoice.*,myscrm_invoice.customer_name as invoice_customer_name,myscrm_invoice.invoice_date,"
                . ",myscrm_invoice.invoice_company_id,myscrm_contract_company.company_name as contract_company_name"
                . ",myscrm_contract_manage.area_id");

        $query->innerJoin('myscrm_invoice', 'myscrm_invoice.contract_id= myscrm_contract_manage.contract_id')
            ->innerJoin('myscrm_special_approval_invoice', 'myscrm_special_approval_invoice.invoice_no= myscrm_invoice.invoice_no')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        $query->orderBy(['myscrm_contract_manage.contract_code' => SORT_ASC]);
        $query->indexBy('special_approval_invoice_id');
        $models = $query->asArray()->all();
        if (!empty($models)) {
            foreach ($models as $k => $v) {
                //开票公司
                $models[$k]["invoice_company_name"] = ErpBusinessUnitRepository::getBunameByBuguid($v["invoice_company_id"]);
                $models[$k]["principal_name"] = ErpUserRepository::getUserNameByBuguid($v["promise_fulfil_principle"]);
                $models[$k]["area_name"] = ErpBusinessUnitRepository::getBunameByBuguid($v["area_id"]);
            }
        }
        return $models;
    }

    /**
     * 导出特批授权
     */
    public static function getContractSpecialAuthorizeListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        //先查询myscrm_contract_manage 中符合条件的contract_code
        $query = new Query();
        $query->select("myscrm_contract_manage.contract_code")
            ->from("myscrm_contract_manage")
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');

        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['myscrm_contract_manage.contract_property_id' => $property]);

        //签约人
        $query->andFilterWhere(['myscrm_contract_manage.sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        $contrastManages=$query->all(ContractManageEntity::getDb());
        $contractCodes=ArrayHelper::getColumn($contrastManages,'contract_code',false);

        $query = new Query();
        $query->select("myscrm_contract.ContractId,myscrm_contract.organizationId,myscrm_contract.PartyA as customer_name,myscrm_contract.ContractNum as contract_code"
            . ",myscrm_contract.category,myscrm_contract.promiseFulfilTime,myscrm_contract.actualFulfilTime"
            . ",myscrm_contract.BeginOn,myscrm_contract.EndOn,myscrm_contract.EstablishTime"
            . ",principal.UserName as principal_name,"
//            . ",myscrm_organization.UniqueName,myscrm_organization.FriendlyName"
            . ",contractArea.BUName as area_name");
        $query->from('myscrm_contract')
//            ->leftJoin('myscrm_organization', 'myscrm_organization.Id = myscrm_contract.organizationId')
            ->leftJoin('myscrm_erp_user principal', 'principal.userguid = myscrm_contract.promiseFulfilPrinciple')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract.areaId= contractArea.BUGUID');
        $query->andWhere(['myscrm_contract.authType' => '4']);

        //区域id
        //$query->andFilterWhere(['myscrm_contract.areaId' => $areaId]);
        //$query->andFilterWhere(['myscrm_contract.ContractNum' => $contractCodes]);

        //$query->andFilterWhere(['in','myscrm_contract.ContractNum',$contractCodes]);
        if(!(empty($timeType) || empty($timeStart) || empty($timeEnd))){
            $query->andWhere(['in','myscrm_contract.ContractNum',$contractCodes]);
        }

        $query->orderBy(['myscrm_contract.ContractNum' => SORT_ASC]);
        return $query->all(MyscrmContractEntity::getDb());
    }

    /**
     * 获取坏账列表(导出用)
     * @param integer $status 合同状态
     * @param string $property 合同性质
     * @param string $keyword
     * @param string $areaId 区域
     * @param string $signPeopleId 签约人
     * @return mixed
     */
    public function getBadDebtListForExport($status, $property, $keyword, $areaId, $signPeopleId, $timeType, $timeStart, $timeEnd)
    {
        $query = ContractReceivableEntity::find()
            ->select('
            myscrm_contract_receivable.receivable_id
            ,myscrm_contract_manage.contract_code
            ,myscrm_contract_manage.customer_name
            ,myscrm_contract_manage.customer_full_name
            ,myscrm_contract_manage.area_id
            ,myscrm_contract_manage.sign_date
            ,myscrm_contract_manage.input_date
            ,myscrm_contract_manage.sign_amount
            ,myscrm_contract_property.property_name
            ,myscrm_contract_receivable.received_amount
            ,myscrm_contract_receivable.receivable_amount
            ,myscrm_contract_receivable.billing_amount
            ,myscrm_contract_receivable.bad_debt_reason
            ,myscrm_contract_company.company_name as contract_company_name
            ,myscrm_contract_manage.area_id as area_id
            ')
            ->from('myscrm_contract_receivable')
            ->leftJoin('myscrm_contract_manage', 'myscrm_contract_receivable.contract_id = myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_contract_property', 'myscrm_contract_manage.contract_property_id=property_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->where(['myscrm_contract_receivable.is_bad_debt' => 1]);
        // 状态查询
        self::setStatusQuery($query, $status);

        //合同性质
        $query->andFilterWhere(['contract_property_id' => $property]);

        //区域id
        $query->andFilterWhere(['area_id' => $areaId]);

        //签约人
        $query->andFilterWhere(['sign_people_id' => $signPeopleId]);

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($timeType) && !empty($timeStart) && !empty($timeEnd)) {
            switch ($timeType) {
                case self::SIGN_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.sign_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.sign_date', $timeEnd]);
                    break;
                case self::INPUT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.input_date', $timeEnd]);
                    break;
                case self::EFFECT_TIME_TYPE:
                    $query->andWhere(['>', 'myscrm_contract_manage.effect_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_manage.effect_date', $timeEnd]);
                    break;
                case self::PAYMENT_FILE_TIME_TYPE:
                    $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id');
                    $query->andWhere(['>', 'myscrm_contract_payment.input_date', $timeStart]);
                    $query->andWhere(['<', 'myscrm_contract_payment.input_date', $timeEnd]);
                    break;
            }
        }

        $query->orderBy(['myscrm_contract_manage.contract_code' => 'ASC', 'myscrm_contract_receivable.bad_debt_date' => 'DESC']);
        return $query->asArray()->all();

    }


    /**
     * 获取租期即将到期授权
     * @param $expiredDate 到期日
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getAuthorizationExpiredRemind($expiredDate)
    {
        $sql = "select a.PartyA,a.BeginOn,a.EndOn,c.UniqueName,c.FriendlyName,a.ContractId from myscrm_contract as a inner join myscrm_organization c on a.OrganizationId = c.id " .
            " where a.Status=1 and DATE_FORMAT(a.endon,'%Y-%m-%d')=date(:expiredDate)";

        $models = MyscrmContractEntity::getDb()->createCommand($sql)->bindValue([":expiredDate" => $expiredDate])->queryAll();
        if (!empty($models)) {
            foreach ($models as $k => $v) {
                //查询myscrm_contract_manage 中是否存在,如果不存在则剔除当前$k
                $contractManage = ContractManageRepository::getContractManageArrByCode($v["ContractNum"]);
                if (empty($contractManage)) {
                    unset($models[$k]);
                    continue;
                }
                $models[$k] = array_merge($v, $contractManage);
            }
        }
        return array_values($models);
    }

    /**
     * 获取合同签约应用
     * @param $contractId 合同id
     */
    public static function getContractApps($contractId)
    {
        $query = ConfigEntity::getDb()
            ->createCommand("select a.FriendlyName,b.TotalNum
            from myscrm_application a,myscrm_organizationToAuthorize  b
            where a.ApplicationId=b.ApplicationId
            and b.ContractId=:contractId and b.TotalNum>0", [':contractId' => $contractId])
            ->queryAll();
        return $query;
    }

    /**
     * erp获取合同信息
     * @param string $propertyId 合同性质ID
     * @param date|null $startReviewDate 开始审核日期（date|null:date:给定日期以后审核的数据，null：所有审核的合同数据）
     * @param int $page 取数页码,default=1
     * @param int $pageSize 分页大小,default=20
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getContractsForERP($propertyId, $startReviewDate, $page = 1, $pageSize = 20)
    {
        $page = empty($page) ? 1 : $page;
        $pageSize = empty($pageSize) ? 20 : $pageSize;

        $where = 'contract_status = 2';
        $params = [];

        if (!empty($propertyId)) {
            $where .= ' and contract_property_id = :propertyId ';
            $params[':propertyId'] = $propertyId;
        }

        if (!empty($startReviewDate)) {
            $where .= ' and review_date >= :reviewDate ';
            $params[':reviewDate'] = $startReviewDate;
        }
        $models = ContractManageEntity::find()
            ->select('contract_id,contract_code,customer_id,customer_name,top_customer_name,customer_full_name,sign_amount,
            give_work_day,develop_work_day,implement_work_day')
            ->where($where, $params)
            ->orderBy('review_date DESC,modified_on DESC')
            ->offset(($page - 1) * $pageSize)
            ->limit($pageSize)
            ->asArray()
            ->all();

        if (!empty($models)) {
            foreach ($models as $k => $v) {
                $contractModel = MyscrmContractRepository::getContractByContractNum($v["contract_code"]);
                $beginOn = "";
                $endOn = "";
                if (!empty($contractModel)) {
                    $beginOn = $contractModel->BeginOn;
                    $endOn = $contractModel->EndOn;
                }
                $models[$k]["begin_on"]=$beginOn;
                $models[$k]["end_on"]=$endOn;
            }
        }
        return $models;
    }

    /**
     * 根据时间获取合同信息
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getContractListByTime($beginTime, $endTime, $contractGuid = '')
    {
        $sql = "select m.contract_id as ContractGUID, m.contract_code as ContractCode, c.category_name as ContractType, m.customer_id as CustomerGUID, " .
            "'云客' as ContractOrigin, m.sign_amount as SignMoney, m.sign_people_id as SignUserGUID, m.sign_date as SignDate,m.input_date as EntryDate, 1 as ContractQC, m.balance_amount as JsMoney, m.balance_amount as jsAmount, m.income_amount as YjsAmount," .
            "m.contract_property_id as ContractPropertyId, p.property_name as ContractPropertyName, m.top_customer_name as TopCustomerName, m.customer_full_name as CustomerFullName, " .
            "c.category_name as CategoryName, b.big_category_name as BigCategoryName, l.littler_category_name as LittlerCategoryName, m.rent_amount as RentAmount, m.delivery_amount as DeliveryAmount, " .
            "m.application_deal_amount as ApplicationDealAmount, m.draw_project_numeric as DrawProjectNumeric, m.back_amount as BackAmount, m.back_amount as fkAmount, m.income_amount as IncomeAmount, m.attribute_name as AttributeName, " .
            "m.repayment_type as RepaymentType, m.repayment_sure as RepaymentSure, m.repayment_company as RepaymentCompany, mcc.company_name as ContractLyQy, m.customer_full_name as QyCstName, m.erp_contract_code as ErpContractCode, " .
            "m.area_id as BUGUID, m.erp_contract_id as ERPContractGUID, " .
            "m.effect_date as Sxdate " .
            "from myscrm_contract_manage as m " .
            "left join myscrm_contract_property as p on m.contract_property_id=p.property_id " .
            "left join myscrm_contract_company as mcc on m.company_id=mcc.company_id " .
            "left join myscrm_contract_category as c on c.category_id=m.category_id " .
            "left join myscrm_contract_big_category as b on b.big_category_id=m.big_category_id " .
            "left join myscrm_contract_littler_category as l on l.littler_category_id=m.littler_category_id " .
            "where m.is_cancelled = 0 and m.contract_status=2 ";

        $bindValues = [];
        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= "and input_date>=:beginTime and input_date<=:endTime ";

            $bindValues[':beginTime'] = $beginTime;
            $bindValues[':endTime'] = $endTime;
        }

        if(! empty($contractGuid)) {
            $sql .= "and m.contract_id =:contractGuid";

            $bindValues['contractGuid'] = $contractGuid;
        }

        $data = ContractManageEntity::getDb()->createCommand($sql, $bindValues)->queryAll();

        //处理合同属性转中文 1新增、2混签、3续签 4增购
        foreach ($data as &$contract) {
            if (empty($contract['AttributeName'])) {
                continue;
            }

            $attributeName = [];
            $attributeList = explode(',', $contract['AttributeName']);
            $new = isset($attributeList[0]) ? $attributeList[0] : '';
            $add = isset($attributeList[1]) ? $attributeList[1] : '';
            $continue = isset($attributeList[2]) ? $attributeList[2] : '';
            $zg = isset($attributeList[3]) ? $attributeList[3] : '';

            $checkedValue = 'true';
            if (strcasecmp($new, $checkedValue) == 0) {
                $attributeName[] = '新增';
            }

            if (strcasecmp($add, $checkedValue) == 0) {
                $attributeName[] = '混签';
            }

            if (strcasecmp($continue, $checkedValue) == 0) {
                $attributeName[] = '续签';
            }

            if (strcasecmp($zg, $checkedValue) == 0) {
                $attributeName[] = '增购';
            }

            $contract['AttributeName'] = implode(',', $attributeName);

            $contract["BelongArea"] = ErpBusinessUnitRepository::getBunameByBuguid($contract["BUGUID"]);
        }
        return $data;
    }

    public static function getContractFkList($contractGuid = '')
    {
        $query = ContractManageEntity::find()
            ->select('contract_id as ContractGUID, contract_code as ContractCode, repayment_sure as Isfk, repayment_time as fkDate, back_amount as fkAmount')
            ->where(['is_cancelled' => 0])
            ->andWhere(['>', 'contract_status', 0]);

        if(! empty($contractGuid)) {
            $query->andWhere(['=', 'contract_id', $contractGuid]);
        }

        return $query->asArray()->all();
    }

    /**
     * 根据时间获取合同信息
     * @param $beginTime
     * @param $endTime
     * @return array
     */
    public static function getContractListByTimeNoAsFields($beginTime, $endTime)
    {
        $query = ContractManageEntity::find()
            ->select("m.contract_code as ContractCode, m.customer_name, m.customer_full_name, p.property_name, c.category_name, b.big_category_name, " .
                "l.littler_category_name,m.input_date, m.sign_date, m.sign_amount, m.contract_id, m.sign_people_id,m.area_id, " .
                "GROUP_CONCAT(ai.`application_name`) AS application_name, SUM(cr.`billing_amount`) AS BillAmount, SUM(cr.`receivable_amount`) AS PaymentAmount ")
            ->from('myscrm_contract_manage as m')
            ->leftJoin('myscrm_contract_property as p', 'm.contract_property_id=p.property_id')
            ->leftJoin('myscrm_contract_category as c', 'c.category_id=m.category_id')
            ->leftJoin('myscrm_contract_big_category as b', 'b.big_category_id=m.big_category_id')
            ->leftJoin('myscrm_contract_littler_category as l', 'l.littler_category_id=m.littler_category_id')
            ->leftJoin('myscrm_contract_receivable as cr', 'cr.`contract_id`=m.`contract_id`')
            ->leftJoin('myscrm_contract_application as ca', 'm.`contract_id` = ca.`contract_id`')
            ->leftJoin('myscrm_application_info as ai', 'ai.`application_id` = ca.`application_id`')
            ->where(['m.is_cancelled' => 0]);

        if (!empty($beginTime) && !empty($endTime)) {
            $query->andWhere(['>=', 'input_date', $beginTime]);
            $query->andWhere(['<=', 'input_date', $endTime]);
        }

        $data = $query->groupBy('m.contract_id')->asArray()->all();

        foreach ($data as &$item) {
            $item["UserName"] = ErpUserRepository::getUserNameByBuguid($item["sign_people_id"]);
            $item["BUName"] = ErpBusinessUnitRepository::getBunameByBuguid($item["area_id"]);
        }

        return $data;
    }

    /**
     * 根据时间获取签约业绩
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getSignAchievementsByTime($beginTime, $endTime)
    {
        $sql = "select myscrm_sign_achievement.contract_id, myscrm_contract_manage.contract_code, myscrm_contract_littler_category.littler_category_name, myscrm_sign_achievement.achievement_amount, myscrm_sign_achievement.achievement_belong_time " .
            "from myscrm_sign_achievement inner join myscrm_contract_manage on myscrm_contract_manage.contract_id = myscrm_sign_achievement.contract_id " .
            "left join myscrm_contract_littler_category on myscrm_contract_manage.littler_category_id = myscrm_contract_littler_category.littler_category_id" .
            "where myscrm_contract_manage.is_cancelled = 0";
        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and achievement_belong_time >= :beginTime and achievement_belong_time <= :endTime";
        }

        $data = ContractManageEntity::getDb()->createCommand($sql, [':beginTime' => $beginTime, ':endTime' => $endTime])->queryAll();
        return $data;
    }

    /**
     * 根据时间获取回款业绩
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getPaymentInfoByTime($beginTime, $endTime, $contractGuid = '')
    {

        $sql = "select myscrm_contract_payment.payment_id as ReceivedGUID, myscrm_contract_payment.payment_date as ReceivedDate, myscrm_contract_payment.payment_amount as ReceivedMoney," .
            "myscrm_contract_payment.contract_id as ContractGUID,myscrm_contract_payment.finance_code as AutoCode, myscrm_contract_payment.payment_account_name as PayCstName, myscrm_contract_payment.input_date as KxgdDate " .
            "from myscrm_contract_payment inner join myscrm_contract_manage on myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id " .
            "where myscrm_contract_manage.is_cancelled = 0 and myscrm_contract_manage.contract_status > 0 ";

        $bindValues = [];
        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and payment_date >= :beginTime and payment_date <= :endTime";

            $bindValues['beginTime'] = $beginTime;
            $bindValues['endTime'] = $endTime;
        }

        if(! empty($contractGuid)) {
            $sql .= " and myscrm_contract_payment.contract_id = :contractGuid";

            $bindValues['contractGuid'] = $contractGuid;
        }

        $data = ContractManageEntity::getDb()->createCommand($sql, $bindValues)->queryAll();
        return $data;
    }

    /**
     * 款项发票回款关联表
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getInvoicePaymentByTime($beginTime, $endTime, $contractGuid)
    {
        //原SQL
        /*$sql = 'SELECT
     myscrm_contract_payment.contract_id AS ContractGUID,
     myscrm_contract_payment.payment_id AS HKGUID,
     myscrm_invoice_payment.cur_payment_amount AS FactMoney,
     myscrm_invoice_payment.invoice_payment_id AS KXFPHKRelaGUID,
     myscrm_invoice_receivable.receivable_id AS KXGUID,
     myscrm_invoice_receivable.invoice_id AS KPGUID,
     myscrm_invoice_receivable.cur_billing_amount AS KPAmount
 FROM
     myscrm_contract_payment
         INNER JOIN
     myscrm_invoice_payment ON myscrm_contract_payment.payment_id = myscrm_invoice_payment.payment_id
         INNER JOIN
     myscrm_invoice_receivable ON myscrm_invoice_receivable.invoice_receivable_id = myscrm_invoice_payment.invoice_receivable_id
         INNER JOIN
     myscrm_contract_manage ON myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id
 WHERE
     myscrm_contract_manage.is_cancelled = 0 and myscrm_contract_manage.contract_status > 0 ';*/

        //新SQL
        $sql ='SELECT
	myscrm_contract_manage.contract_id AS ContractGUID,
	myscrm_contract_payment.payment_id AS HKGUID,
	myscrm_invoice_payment.cur_payment_amount AS FactMoney,
	myscrm_invoice_payment.invoice_payment_id AS KXFPHKRelaGUID,
	myscrm_invoice_receivable.receivable_id AS KXGUID,
	myscrm_invoice_receivable.invoice_id AS KPGUID,
	myscrm_invoice_receivable.cur_billing_amount AS KPAmount
FROM
	myscrm_contract_manage
INNER JOIN myscrm_contract_receivable ON myscrm_contract_manage.contract_id = myscrm_contract_receivable.contract_id
INNER JOIN myscrm_invoice_receivable ON myscrm_contract_receivable.receivable_id = myscrm_invoice_receivable.receivable_id
INNER JOIN myscrm_invoice ON myscrm_invoice.invoice_id = myscrm_invoice_receivable.invoice_id
LEFT JOIN myscrm_invoice_payment ON myscrm_invoice_payment.invoice_receivable_id = myscrm_invoice_receivable.invoice_receivable_id
LEFT JOIN myscrm_contract_payment ON myscrm_contract_payment.payment_id = myscrm_invoice_payment.payment_id
WHERE
	myscrm_contract_manage.is_cancelled = 0
AND myscrm_contract_manage.contract_status > 0
';

        $beginTime = strtotime($beginTime)<0?null:$beginTime;
        $endTime = strtotime($endTime)>time()?null:$endTime;

        //$beginTime = date("Y-m-d H:i:s",$beginTime);
        //$endTime = date("Y-m-d H:i:s",$endTime);

        $bindValues = [];
        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and myscrm_invoice_payment.created_on >= :beginTime and myscrm_invoice_payment.created_on <= :endTime";

            $bindValues['beginTime'] = $beginTime;
            $bindValues['endTime'] = $endTime;
        }

        if(! empty($contractGuid)) {
            $sql .= " and myscrm_contract_manage.contract_id = :contractGuid";

            $bindValues['contractGuid'] = $contractGuid;
        }

        $data = ContractManageEntity::getDb()->createCommand($sql, $bindValues)->queryAll();
        return $data;
    }

    /**
     * 删除待录入回款校验
     * @param $payment_id
     * @param $delpayment
     * @return bool
     */
    public static function getInvoicePaymentByPaymentId($payment_id,$delpayment=null){

        $res = InvoicePaymentEntity::find()->where(["payment_id"=>$payment_id])->select("payment_id")->asArray()->all();

        if(!empty($delpayment)){// 删除合同回款
            die('该功能暂不开放');
            $model = new ContractPaymentService;
            $result = $model->deleteContractPayment($payment_id,$delpayment);

            return $result;
        }

        if(!empty($res)){//合同表被关联返回false 不可删除
            return false;
        }

        return true;
    }


    /**
     * 款项发票回款关联表
     * @param $beginTime
     * @param $endTime
     * @return array
     * @throws \yii\db\Exception
     */
    public static function getReceivableInfoByTime($beginTime, $endTime, $contractId = '')
    {

        $sql = "select myscrm_contract_receivable.receivable_id as ShouldGetGUID, myscrm_contract_receivable.contract_id as ContractGUID, myscrm_receivable_item.receivable_item_name as IteamName, " .
            "myscrm_money_category_item.money_category_item_name as IteamType, myscrm_contract_receivable.payee_id as GetIner, myscrm_contract_receivable.receivable_date as GetDate, " .
            "myscrm_contract_receivable.receivable_amount as GetMoney, myscrm_contract_receivable.payment_condition as PayCondition, myscrm_buss_account_types.finance_type_name as GetType,myscrm_buss_account_types.type_name as IncomeType, " .
            "myscrm_contract_receivable.receivable_type as ReceivableType, myscrm_contract_receivable.is_bad_debt as IsBadDebt, myscrm_contract_receivable.is_operation as IsOperation, " .
            "myscrm_contract_receivable.remark AS Remark, myscrm_contract_manage.balance_amount AS JsMoney " .
            "from myscrm_contract_receivable left join myscrm_contract_manage on myscrm_contract_manage.contract_id = myscrm_contract_receivable.contract_id " .
            "left join myscrm_receivable_item on myscrm_receivable_item.receivable_item_id = myscrm_contract_receivable.receivable_item_id " .
            "left join myscrm_money_category_item on myscrm_money_category_item.money_category_item_id = myscrm_contract_receivable.money_category_item_id " .
            "left join myscrm_buss_account_types on myscrm_buss_account_types.type_id = myscrm_contract_receivable.revenue_type " .
            "where myscrm_contract_manage.is_cancelled = 0  and myscrm_contract_manage.contract_status = 2 ";

        $params = [];
        $addParams = [];
        if (!empty($beginTime) && !empty($endTime)) {
            $addParams = [
                ':beginTime' => $beginTime,
                ':endTime' => $endTime,
            ];
            $params = $params + $addParams;

            $sql .= " and myscrm_contract_receivable.receivable_date >= :beginTime and myscrm_contract_receivable.receivable_date <= :endTime ";
        }
        if(! empty($contractId)){
            $addParams = array_merge($addParams, [':contractId' => $contractId]);
            $params = $params + $addParams;

            $sql .= " and myscrm_contract_receivable.contract_id = :contractId";
        }

        return ContractManageEntity::getDb()->createCommand($sql, $params)->queryAll();
    }

    /**
     * 根据时间获取发票信息
     * @param $beginTime
     * @param $endTime
     * @return array
     * @throws \yii\db\Exception
     */
    public static function getInvoicesByTime($beginTime, $endTime, $contractGuid = '')
    {
        $sql = "select myscrm_invoice.invoice_id as ReceiptGUID, myscrm_invoice.invoice_no as ReceiptCode, if(myscrm_invoice.invoice_category = 0,'销售票','服务票') as ReceiptType, myscrm_invoice.invoice_amount as ReceiptMoney," .
            "myscrm_invoice_business_unit.BUName as ReceiptCompany, myscrm_invoice.invoice_date as ReceiptDate, myscrm_invoice.contract_id as ContractGUID,myscrm_invoice.is_lose as ReceiptIsLose,myscrm_invoice.customer_name as ReceiptCustomerName, " .
            "myscrm_invoice.red_by_invoice_id, myscrm_invoice.is_red_invoice, myscrm_invoice.is_receipt, " .
            "myscrm_invoice.invoice_code as FpNum, myscrm_invoice.customer_name as CstName, myscrm_invoice.is_lose as IsYs, myscrm_invoice.contract_id as ContractGuid, myscrm_invoice.is_red_invoice as IsHc, " .
            "myscrm_invoice.red_by_invoice_id as HcReceiptGuid, myscrm_invoice.invoice_type as FpLx, " .
            "myscrm_invoice.remark as Bz, " .
            "myscrm_invoice.proposer_id as SqrUser, myscrm_invoice.created_on as CreateDate, " .
            "myscrm_special_approval_invoice.category as YcType, myscrm_special_approval_invoice.promise_fulfil_time as YcJhDate, " .
            "myscrm_special_approval_invoice.actual_fulfil_time as YcSjDate " .
            "from myscrm_invoice left join myscrm_contract_manage on myscrm_contract_manage.contract_id = myscrm_invoice.contract_id " .
            "left join myscrm_invoice_business_unit on myscrm_invoice_business_unit.BUGUID = myscrm_invoice.invoice_company_id " .
            "left join myscrm_special_approval_invoice on myscrm_special_approval_invoice.invoice_no = myscrm_invoice.invoice_no " .
            "WHERE 1=1 ";

        $bindValues = [];
        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and myscrm_invoice.invoice_date >= :beginTime and myscrm_invoice.invoice_date <= :endTime";

            $bindValues['beginTime'] = $beginTime;
            $bindValues['endTime'] = $endTime;
        }
        if (! empty($contractGuid)) {
            $sql .= " and myscrm_invoice.contract_id = :contractGuid";

            $bindValues['contractGuid'] = $contractGuid;
        }

        return ContractManageEntity::getDb()->createCommand($sql, $bindValues)->queryAll();
    }

    /**
     * 根据时间获取租户信息
     * @param $beginTime
     * @param $endTime
     * @return array
     */
    public static function getProductInfoByTime($beginTime, $endTime, $contractGuid = '')
    {
        $sql = "select myscrm_contract_application.contract_application_id as ProductGUID, case myscrm_contract_application.contract_type when 1 then '新签' when 2 then '增购' when 3 then '续签' else '' end as CloudSignType, myscrm_application_info.application_name as ProductName, myscrm_contract_application.contract_id as ContractGUID " .
            ", myscrm_contract_application.rent_period AS Rent_Time, myscrm_contract_application.application_id AS ProductInfoID, myscrm_contract_application.authorization_numeric AS Quantity " .
            "from myscrm_contract_application inner join myscrm_contract_manage on myscrm_contract_manage.contract_id = myscrm_contract_application.contract_id " .
            "left join myscrm_application_info on myscrm_contract_application.application_id = myscrm_application_info.application_id " .
            "where myscrm_contract_manage.is_cancelled = 0 and myscrm_contract_manage.contract_status > 0 ";

        $bindValues = [];
        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and myscrm_contract_application.created_on >= :beginTime and myscrm_contract_application.created_on <= :endTime";

            $bindValues[':beginTime'] = $beginTime;
            $bindValues[':endTime'] = $endTime;
        }

        if(! empty($contractGuid)) {
            $sql .= " and myscrm_contract_application.contract_id = :contractGuid";

            $bindValues[':contractGuid'] = $contractGuid;
        }

        return ContractManageEntity::getDb()->createCommand($sql, $bindValues)->queryAll();
    }

    /**
     * 根据contract_code取数据
     * @param $code
     * @return array|null|ContractManageEntity
     */
    public function getContractManageByCode($code)
    {
        return ContractManageEntity::find()->where(["contract_code" => $code])->one();
    }

    /**
     * 根据contract_id取数据
     * @param $code
     * @return array|null|ContractManageEntity
     */
    public function getContractByContractIdOrErpContractId($contractId)
    {
        return ContractManageEntity::find()->where(["or",["contract_id" => $contractId],["erp_contract_id" => $contractId]])->one();
    }
    /**
     * 根据会签编号获取合同
     * @param $code
     * @return array|null|ContractManageEntity
     */
    public function getContractBySignNumber($signNumber)
    {
        return ContractManageEntity::find()->where(["sign_number" => $signNumber])->one();
    }
    /**
     * 根据contract_code取数据
     * @param $code
     * @return array|null|\yii\db\ActiveRecord
     */
    public function getContractManageArrByCode($code)
    {
        return ContractManageEntity::find()->where(["contract_code" => $code])->asArray()->one();
    }


    /**
     * 删除合同
     * @param $contractId
     * @return int
     */
    public static function deleteByContractId($contractId)
    {
        /** @var MyscrmContractSyncDeleteDataRepository $contractSyncDeleteDataRepository */
        $contractSyncDeleteDataRepository = \Yii::createObject(MyscrmContractSyncDeleteDataRepository::className());

        $deleteResult = ContractManageEntity::deleteAll(["contract_id" => $contractId]);

        if ($deleteResult > 0) {
            $contractSyncDeleteDataRepository->add('myscrm_contract_manage', 'contract_id', $contractId);
        }

        return $deleteResult;
    }

    /**
     * 搜索合同
     * @param $keyword
     * @param $pageNo
     * @param $limit
     * @return array|ContractManageEntity[]
     */
    public static function search($keyword, $pageNo, $limit)
    {
        //根据myscrm_erp_user中的UserName 查找
        $data=ErpUserEntity::find()->select("UserGUID")->where(['like','UserName',$keyword])->all();
        $sign_people_ids=[];
        foreach($data as $item){
            array_push($sign_people_ids,$item["UserGUID"]);
        }

        $offset = max($pageNo - 1, 0) * $limit;
        $query = ContractManageEntity::find()
            ->select([
                'myscrm_contract_manage.contract_id',
                'myscrm_contract_manage.contract_code',
                'myscrm_contract_manage.customer_id',
                'myscrm_contract_manage.customer_name',
                'myscrm_contract_manage.customer_full_name',
                'myscrm_contract_manage.top_customer_name',
                'myscrm_contract_manage.sign_date',
                'myscrm_contract_manage.sign_people_id',
                'myscrm_contract_manage.sign_amount',
            ])
            ->where([
                'or',
                ['like', 'myscrm_contract_manage.contract_code', $keyword],
                ['like', 'myscrm_contract_manage.customer_name', $keyword],
                ['like', 'myscrm_contract_manage.top_customer_name', $keyword],
                ['like', 'myscrm_contract_manage.customer_full_name', $keyword],
                ['in', 'myscrm_contract_manage.sign_people_id', $sign_people_ids]
            ])
            ->andWhere([
                'contract_status' => 2,
                'is_cancelled' => 0,
            ])
            ->orderBy('myscrm_contract_manage.sign_date DESC')
            ->limit($limit)->offset($offset)->asArray();

        $models=$query->all();
        foreach ($models as $k => $v) {
            $sign_people = "";
            $email = "";
            $mobile = "";
            $user = ErpUserRepository::getUserById($v["sign_people_id"]);
            if (!empty($user)) {
                $sign_people = $user->UserName;
                $email = $user->Email;
                $mobile = $user->MobilePhone;
            }
            $models[$k]["sign_people"] = $sign_people;
            $models[$k]["email"] = $email;
            $models[$k]["mobile"] = $mobile;

        }
        return $models;
    }

    public static function deleteDistributionByContractId($contractId)
    {
        try {
            /** @var MyscrmContractSyncDeleteDataRepository $contractSyncDeleteDataRepository */
            $contractSyncDeleteDataRepository = \Yii::createObject(MyscrmContractSyncDeleteDataRepository::className());

            $deleteData = MyscrmContractDistributionAmountEntity::findAll(["contract_id" => $contractId]);

            $deleteResult = MyscrmContractDistributionAmountEntity::deleteAll(["contract_id" => $contractId]);
            //坏味道修复

            if ($deleteResult > 0) {
                foreach ($deleteData as $data) {
                    $contractSyncDeleteDataRepository->add('myscrm_contract_distribution_amount', 'amount_id', $data['amount_id']);
                }
            }

            return $deleteResult;

        } catch (\Exception $e) {
            \Yii::error($e->getMessage());
            return self::setAndReturn(self::getErrMsg());
        }

        return true;
    }

    public static function addDistribution($contractId, $inputDate, $distribution)
    {
        $entity = new MyscrmContractDistributionAmountEntity();
        $contractModel = ContractManageRepository::getContractManageModelByContractId($contractId); //获取model

        $sumPayment = ContractPaymentRepository::getPaymentSumByContractId($contractId);

        $contract_property_id = $contractModel->contract_property_id;

        $allowArr= [
            ContractPropertyEntity::MYYK_PROPERTY_DIRECTLY_UNDER_ID,
            ContractPropertyEntity::MYYK_PROPERTY_JOINT_OPERATION_ID,
            ContractPropertyEntity::MYYK_PROPERTY_DIRECT_SALES_ID,
//            '7f83719a-fce8-11e4-bed8-00155d02c832',
//            '7f8422d0-fce8-11e4-bed8-00155d02c832',
        ];

        $propertyType = in_array($contract_property_id,$allowArr);

        $entity->isNewRecord = true;
        $entity->amount_id = StringHelper::uuid();
        $entity->sign_average_amount = $distribution['sign_average_amount'];
        $entity->settlement_average_amount = $distribution['settlement_average_amount'];
        //TODO 如果回款不为空的话那么需要将回款分摊金等于结算业绩分摊金

        $entity->receivable_average_amount = isset($distribution['receivable_average_amount']) ? $distribution['receivable_average_amount'] : 0;
        $entity->contract_type = $distribution['contract_type'];
        $entity->contract_id = $contractId;
        $entity->user_id = $distribution['user_id'];
        $entity->area_id = $distribution['area_id'];
        $entity->department_id = $distribution['department_id'];
        $entity->CreatedOn = date('Y-m-d H:i:s');
        $entity->CreatedBy = isset(\Yii::$app->user->identity->userguid) ? \Yii::$app->user->identity->userguid : '';
        $entity->input_date = $inputDate;


        $entity->establish_time = $propertyType?$inputDate:$distribution['establish_time'];//TODO 记得如果是云客签约-直属/联营这2类合同的，开通时间=合同录入时间。
        $entity->modified_on = date('Y-m-d H:i:s');
        $entity->is_saas = $distribution['is_saas'];

        try {
            return $entity->save();
        } catch (\Exception $e) {
            \Yii::error($e->getMessage());
            return self::setAndReturn(self::getErrMsg());
        }
    }

    public static function updateDistribution($amountId, $updateAmount)
    {
        try {
            $entity = MyscrmContractDistributionAmountEntity::findOne(['amount_id' => $amountId]);
            if ($entity) {
                $entity->receivable_average_amount = $updateAmount;
                $entity->modified_on = date('Y-m-d H:i:s');
                return $entity->save();
            }
        } catch (\Exception $e) {
            \Yii::error($e->getMessage());
            return false;
        }

        return true;
    }

    public static function getDistributionByContractId($contractId)
    {
        return MyscrmContractDistributionAmountEntity::find()
            ->where(['contract_id' => $contractId])
            ->asArray()
            ->all();
    }

    public static function checkHasDistributionByContractId($contractId)
    {
        return MyscrmContractDistributionAmountEntity::find()
            ->where(['contract_id' => $contractId])
            ->exists();
    }

    public static function checkHasDistributionByContractIdAndType($contractId, $contractType, $saasType)
    {
        return MyscrmContractDistributionAmountEntity::find()
            ->where(['contract_id' => $contractId])
            ->andWhere(['contract_type' => $contractType])
            ->andWhere(['is_saas' => $saasType])
            ->exists();
    }

    /**
     * @param $contractId
     * @param string $contractType
     * @param string $saasType
     * @param string $userId
     * @return array|MyscrmContractDistributionAmountEntity|null
     */
    public static function getDistributionEntity($contractId, $contractType = '', $saasType = '', $userId = '')
    {
        return MyscrmContractDistributionAmountEntity::find()
            ->where(['contract_id' => $contractId])
            ->andFilterWhere(['user_id' => $userId])
            ->andFilterWhere(['contract_type' => $contractType])
            ->andFilterWhere(['is_saas' => $saasType])
            ->one();
    }

    public static function getDistributionEntityWithoutUserId($contractId, $contractType = '', $userId = [])
    {
        return MyscrmContractDistributionAmountEntity::find()
            ->where(['contract_id' => $contractId])
            ->andFilterWhere(['not', ['user_id' => $userId]])
            ->andFilterWhere(['contract_type' => $contractType])
            ->one();
    }

    /**
     * 更新entity
     * @param $entity MyscrmContractDistributionAmountEntity
     * @return bool
     */
    public static function updateDistributionEntity($entity)
    {
        try {
            $entity->modified_on = date('Y-m-d H:i:s');
            return $entity->save();
        } catch (\Exception $e) {
            \Yii::error($e->getMessage());
            return false;
        }
    }

    /**
     * 批量根据contract_code 获取contract_id
     * @param $codes
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getContractMangeIdsByCode($codes){
        return ContractManageEntity::find()->where(["contract_code"=>$codes])->select("contract_id,contract_code")->all();
    }

    public function getContractsByCode($codes)
    {
        return ContractManageEntity::find()->where(["contract_code"=>$codes])->all();
    }


    /**
     * 获取合同合同应结算金
     * @param $contractId
     * @return bool|string
     */
    public static function getContractIncomeAmount($contractId)
    {
        return ContractManageEntity::find()->select('income_amount')->where(['contract_id' => $contractId])->scalar();
    }



    /**
     * 获取所有有效合同的ID
     *
     * @return object|false
     */
    public static function getAllContractID(){
        return ContractManageEntity::find()->select('contract_id')->where(['is_cancelled' => 0,'contract_status'=> 2])->asArray()->all();
    }

    /**
     * @param $ContractGuid
     * @param $beginTime
     * @param $endTime
     * @return \yii\db\DataReader
     * @throws \yii\db\Exception
     */
    public static function getAllOrder($ContractGuid,$beginTime,$endTime){
        $where = '';
        if($ContractGuid){
            $where .= ' AND c.contract_id = \''.$ContractGuid.'\'';
        }
        if($beginTime){
            $where .= ' AND a.CreatedOn >= \''.$beginTime.'\'';
        }
        if($endTime){
            $where .= ' AND a.CreatedOn <= \''.$endTime.'\'';
        }
        $myscrm_organizationtoauthorize = MyscrmOrganizationToAuthorizeEntity::globalTableName();
        $myscrm_contract = MyscrmContractEntity::globalTableName();
        $myscrm_application_info = ApplicationInfoEntity::globalTableName();
        $myscrm_application = MyscrmApplicationEntity::globalTableName();
        $myscrm_contract_application = ContractApplicationEntity::globalTableName();
        $myscrm_contract_manage = ContractManageEntity::globalTableName();
        $sql = "SELECT
            BeginOn,
            EndOn,
            PerformanceAccountingTime,
            ContractApplicationId,
            b.ApplicationId,
            application_info_id,
            TotalNum,
            application_name,
            FriendlyName,
            SettlementAmount,
            c.contract_id,
            rent_period,
            a.order_code as DeliveryCode,
            a.order_guid as DeliveryGUID,
            a.CreatedOn,
            CASE
                f.contract_type 
                WHEN 1 THEN
                '新签' 
                WHEN 2 THEN
                '增购' 
                WHEN 3 THEN
                '续签' 
                ELSE ''
            END AS CloudSignType
        FROM
            {$myscrm_contract} a
            LEFT JOIN {$myscrm_organizationtoauthorize} b ON a.ContractId = b.ContractId
            INNER JOIN {$myscrm_contract_manage} c ON c.contract_code = a.ContractNum
            LEFT JOIN {$myscrm_application_info} d ON d.application_id = b.application_info_id
            LEFT JOIN {$myscrm_application} e ON e.ApplicationId = b.ApplicationId
            LEFT JOIN {$myscrm_contract_application} f ON f.contract_application_id = b.ContractApplicationId 
        WHERE
            b.TotalNum > 0 
            AND b.SettlementAmount >= 0";
        $conn = ContractManageEntity::getDb();
        return $conn->createCommand($sql.$where)->query();
    }


    public static function getIllegalErpContract(){
        $sql = 'select erp_contract_code,erp_contract_id from contract_yk.myscrm_contract_manage where (erp_contract_code is not null and erp_contract_code <>"" ) and (erp_contract_id is null or erp_contract_id = "")';
        $conn = ContractManageEntity::getDb();
        return $conn->createCommand($sql)->query();
    }


    public static function getSignAmount($contract_id){
        return floatval(ContractManageEntity::find()->select('sign_amount')->where(['contract_id' => $contract_id])->sum('sign_amount'));
    }


    public static function getDirectContract($beginTime,$endTime){
        $beginTime = '';
        $endTime = '';

        $myscrm_contract_littler_category = ContractLittlerCategoryEntity::globalTableName();
        $myscrm_contract_manage = ContractManageEntity::globalTableName();
        $myscrm_contract_property = ContractPropertyEntity::globalTableName();
        $myscrm_erp_business_unit = ErpBusinessUnitEntity::globalTableName();
        $myscrm_erp_user = ErpUserEntity::globalTableName();
        $sql = "
        SELECT
	m.contract_id,
	p.property_name,
	'云客' bu_name,
	m.input_date,
	m.customer_name,
	m.top_customer_name,
	m.customer_full_name,
	m.sign_date,
	u.BUName area_name,
	m.contract_code,
	m.sign_amount,
	mclc.littler_category_name type_name,
	UserName sign_people_name,
	DepartmentGUID department_guid,
	ss.BUName department_name,
	case m.littler_category_id
		when '62f81bf4-fcf4-11e4-bed8-00155d02c832' then 'SAAS合同'
		when '62f81ca9-fcf4-11e4-bed8-00155d02c832' then 'SAAS合同'
		when '62f81rt6-fcf4-11e4-bed8-00155d02c832' then '广告合同'
		else ''
	end as business_type_name,
	case m.littler_category_id
		when '62f81bf4-fcf4-11e4-bed8-00155d02c832' then '云客租用服务收入项'
		when '62f81ca9-fcf4-11e4-bed8-00155d02c832' then '云客增值服务收入项'
		when '62f81rt6-fcf4-11e4-bed8-00155d02c832' then '云客互联网服务收入项'
		else ''
	end as finance_type_name
FROM
	{$myscrm_contract_manage} m
	LEFT JOIN {$myscrm_contract_littler_category} mclc ON m.littler_category_id = mclc.littler_category_id
	LEFT JOIN {$myscrm_contract_property} p ON m.contract_property_id = p.property_id
	LEFT JOIN {$myscrm_erp_business_unit} u ON m.area_id = u.BUGUID
	LEFT JOIN {$myscrm_erp_user} us ON m.sign_people_id = us.UserGUID
	LEFT JOIN {$myscrm_erp_business_unit} ss ON m.sign_people_department_by = ss.BUGUID
 ";

        $where =  ' where property_name  = \'云客直销\'';
        $conn = ContractManageEntity::getDb();
        return $conn->createCommand($sql.$where)->query();
    }


    public static function getDirectContractPayment($beginTime,$endTime){
        $beginTime = '';
        $endTime = '';

        $myscrm_contract_littler_category = ContractLittlerCategoryEntity::globalTableName();
        $myscrm_contract_manage = ContractManageEntity::globalTableName();
        $myscrm_contract_property = ContractPropertyEntity::globalTableName();
        $myscrm_erp_business_unit = ErpBusinessUnitEntity::globalTableName();
        $myscrm_erp_user = ErpUserEntity::globalTableName();
        $myscrm_contract_payment = ContractPaymentEntity::globalTableName();
        $sql = "
SELECT
	m.contract_id,
	cp.payment_id,
	p.property_name,
	'云客' bu_name,
	m.input_date,
	m.customer_name,
	m.top_customer_name,
	m.customer_full_name,
	m.sign_date,
	u.BUName area_name,
	m.contract_code,
	m.sign_amount,
	cp.payment_date,
	cp.payment_amount,
	cp.input_date payment_input_date,
	mclc.littler_category_name busi_type_name,
	UserName sign_people_name,
	DepartmentGUID department_guid,
	ss.BUName department_name,
	case m.littler_category_id
		when '62f81bf4-fcf4-11e4-bed8-00155d02c832' then 'SAAS合同'
		when '62f81ca9-fcf4-11e4-bed8-00155d02c832' then 'SAAS合同'
		when '62f81rt6-fcf4-11e4-bed8-00155d02c832' then '广告合同'
		else ''
	end as business_type_name,
	case m.littler_category_id
		when '62f81bf4-fcf4-11e4-bed8-00155d02c832' then '云客租用服务收入项'
		when '62f81ca9-fcf4-11e4-bed8-00155d02c832' then '云客增值服务收入项'
		when '62f81rt6-fcf4-11e4-bed8-00155d02c832' then '云客互联网服务收入项'
		else ''
	end as finance_type_name,
	'增购' receiva_type,
	'云客' organize,
	'yk' Origin
FROM
	{$myscrm_contract_payment} cp
	INNER JOIN {$myscrm_contract_manage} m ON m.contract_id = cp.contract_id
	LEFT JOIN {$myscrm_contract_littler_category} mclc ON m.littler_category_id = mclc.littler_category_id
	LEFT JOIN {$myscrm_contract_property} p ON m.contract_property_id = p.property_id
	LEFT JOIN {$myscrm_erp_business_unit} u ON m.area_id = u.BUGUID
	LEFT JOIN {$myscrm_erp_user} us ON m.sign_people_id = us.UserGUID
	LEFT JOIN {$myscrm_erp_business_unit} ss ON m.sign_people_department_by = ss.BUGUID
 WHERE contract_property_id = 'efa353cd-51ca-11e8-a035-00155d02c83e'";

        $conn = ContractManageEntity::getDb();
        return $conn->createCommand($sql)->query();
        return null;
    }
}
